const { Employee, Department, WorkOrder } = require('../../database/index')
const sequelize = require('sequelize');
const { getExecutedSql } = require('../../logger');
const { performDatabaseQuery, getDepartmentName } = require('./workOrderAnalysis');

// 导出
module.exports = async (req, res) =>
{
    const operation = req.params.operation;
    const { workOrderStatus, startDate, endDate, employeeName } = req.body;
    try {
        switch (operation) {
            // 查询部门人数
            case 'findCount':
                const result = await Employee.findAll({
                    attributes: ['DepartmentID', [sequelize.fn('COUNT', sequelize.col('DepartmentID')), 'totalEmployees']],
                    group: ['DepartmentID'],
                });
                for (let index = 0; index < result.length; index++) {
                    const department = await Department.findOne({ where: { DepartmentID: result[index].DepartmentID } })
                    // 创建DepartmentName字段

                    result[index].dataValues.DepartmentName = department.DepartmentName;
                }
                // 检查result的类型
                if (result.length === 0) {
                    res.status(200).send({
                        result: [],
                        meta: {
                            msg: "暂无数据",
                            status: 200
                        }
                    })
                } else {
                    // 根据ID查找相关部门
                    res.status(200).send({
                        result,
                        meta: {
                            msg: "查询成功",
                            status: 200
                        },
                        sql: getExecutedSql()
                    })
                    break;
                }
            // 统计工单完成最多或最少的部门
            case 'findWorkOrderCount':
                const totalWorkOrders = await WorkOrder.findAll({
                    where: {
                        Status: workOrderStatus,
                    },
                });
                const count = await WorkOrder.count({
                    where: {
                        Status: workOrderStatus,
                    },
                })
                console.log('总工单数(Status为1)：', totalWorkOrders);
                res.status(200).send({
                    data: {
                        result: totalWorkOrders,
                        count: count
                    },
                    meta: {
                        msg: "获取成功",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            // 统计员工工作总数据
            case "findAllData":
                try {
                    // 默认查询日期为{"endDate": "2021-12-31""startDate": "2021-01-01"}，其他日期以前端输入为主，不输入就全部查找
                    const results = await performDatabaseQuery(startDate, endDate, employeeName);
                    console.log(results); // 打印查询结果

                    // 转换查询结果
                    const transformedResults = results.map(item => ({
                        WeekStart: item.WeekStart,
                        MonthStart: item.MonthStart,
                        WeeklyCompletedOrders: item.WeeklyCompletedOrders,
                        MonthlyCompletedOrders: item.MonthlyCompletedOrders,
                        EmployeeName: item['employee.Name'],                 // 将属性 employee.Name 重命名为 Name
                        DepartmentID: item['employee.DepartmentID'], // 将属性 employee.DepartmentID 重命名为 DepartmentID
                        WorkOrderName: item['workOrder.WorkOrderName'],     // 将属性 workOrder.WorkOrderName 重命名为 WorkOrderName
                        CreatedDate: item['workOrder.CreatedDate'],         // 将属性 workOrder.CreatedDate 重命名为 CreatedDate
                        Income: item['workOrder.Income']
                    }));
                    for (const item of transformedResults) {
                        item.Departments = await Department.findOne({ where: { DepartmentID: item.DepartmentID } })
                    }
                    res.status(200).send({ message: '查询成功', data: transformedResults, sql: getExecutedSql() });
                    break
                } catch (error) {
                    console.error('Error:', error);
                }
            // 统计部门和员工最佳和最差的
            case 'findWorkOrder':
                try {
                    // 转换查询结果
                    const results = await performDatabaseQuery();
                    const transformedResults = results.map(item => ({
                        WeekStart: item.WeekStart,
                        MonthStart: item.MonthStart,
                        WeeklyCompletedOrders: item.WeeklyCompletedOrders,
                        MonthlyCompletedOrders: item.MonthlyCompletedOrders,
                        EmployeeName: item['employee.Name'],
                        // 将属性 employee.Name 重命名为 Name
                        DepartmentID: item['employee.DepartmentID'],
                        // 将属性 employee.DepartmentID 重命名为 DepartmentID
                        WorkOrderName: item['workOrder.WorkOrderName'],
                        // 将属性 workOrder.WorkOrderName 重命名为 WorkOrderName
                        CreatedDate: item['workOrder.CreatedDate'],
                        // 将属性 workOrder.CreatedDate 重命名为 CreatedDate
                    }));

                    // 创建存储部门和员工完成工单数的对象
                    const departmentCompletedOrders = {};
                    const employeeCompletedOrders = {};

                    // 计算每个部门和员工的完成工单数
                    for (const item of transformedResults) {
                        const { DepartmentID, EmployeeName } = item;

                        // 累加部门的完成工单数
                        departmentCompletedOrders[DepartmentID] = (departmentCompletedOrders[DepartmentID] || 0) + 1;

                        // 累加员工的完成工单数
                        employeeCompletedOrders[EmployeeName] = (employeeCompletedOrders[EmployeeName] || 0) + 1;
                    }

                    // 将部门完成工单数对象转换为键值对数组，并按完成工单数进行排序
                    const sortedDepartmentCompletedOrders = Object.entries(departmentCompletedOrders)
                        .sort((a, b) => b[1] - a[1]);

                    // 将员工完成工单数对象转换为键值对数组，并按完成工单数进行排序
                    const sortedEmployeeCompletedOrders = Object.entries(employeeCompletedOrders)
                        .sort((a, b) => b[1] - a[1]);

                    // 获取完成工单数最多和最少的部门和员工
                    const mostCompletedDepartment = sortedDepartmentCompletedOrders[0];
                    const leastCompletedDepartment = sortedDepartmentCompletedOrders[sortedDepartmentCompletedOrders.length - 1];
                    const mostCompletedEmployee = sortedEmployeeCompletedOrders[0];
                    const leastCompletedEmployee = sortedEmployeeCompletedOrders[sortedEmployeeCompletedOrders.length - 1];


                    // 构建结果对象
                    const result = {
                        mostCompletedDepartment: { // 成工单最多的部门
                            DepartmentName: await getDepartmentName(mostCompletedDepartment[0]),
                            CompletedOrders: mostCompletedDepartment[1]
                        },
                        leastCompletedDepartment: {// 成工单最少的部门
                            DepartmentName: await getDepartmentName(leastCompletedDepartment[1]),
                            CompletedOrders: leastCompletedDepartment[1]
                        },
                        mostCompletedEmployee: { //完成工单最多的员工
                            EmployeeName: mostCompletedEmployee[0],
                            CompletedOrders: mostCompletedEmployee[1]
                        },
                        leastCompletedEmployee: { //完成工单最少的员工
                            EmployeeName: leastCompletedEmployee[0],
                            CompletedOrders: leastCompletedEmployee[1]
                        }
                    };
                    res.status(200).send({ message: '查询成功', data: transformedResults, result });
                } catch (error) {
                    console.error('统计工单部门数量时出错：', error);
                }
                break;
        }

    } catch (error) {
        res.status(500).send({ message: 'Internal server error', error: error.message });
    }
}